﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TpBaseObjects.Objects;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using TpContracts;

namespace TpData.Layer {

    internal class OrganisationDataLayer : BaseSqlDataLayer {

        #region Members

        private string loadOrganisationStatement = @"dbo.GetOrganisation";
        private string loadOrganisationsShortListStatement = "dbo.GetOrganisationShortList";
        private string loadOrganisationPersonsStatement = @"dbo.GetOrganisationPersons";
        private string loadPersonsStatement = @"dbo.GetPersons";
        private string saveOrganisationStatement = @"dbo.SaveOrganisation";
        private string savePersonStatement = @"dbo.SavePerson";
        private string loadBerufstitelStatement = @"dbo.GetBerufstitel";
        private string loadFunktionsStatement = @"dbo.GetFunktionen";
        private string loadRechtsformStatement = @"dbo.GetRechtsformen";

        #endregion

        #region Load

        public virtual BerufstitelObject GetBerufstitel(int berufstitelKey) {
            BerufstitelObject bo = new BerufstitelObject();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(this.loadBerufstitelStatement, new SqlParameter("@BerufstitelKey", berufstitelKey))) {
                foreach (DataRow dr in dt.Rows) {
                    bo.BerufsTitelKey = Convert.ToInt32(dr["BerufstitelKey"]);
                    bo.BerufsTitel = Convert.ToString(dr["BerufstitelBez"]);
                }
            }
            return bo;
        }

        public virtual List<BerufstitelObject> GetBerufstitelList() {
            List<BerufstitelObject> ret = new List<BerufstitelObject>();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(this.loadBerufstitelStatement)) {
                foreach (DataRow dr in dt.Rows) {
                    BerufstitelObject bo = new BerufstitelObject();
                    bo.BerufsTitelKey = Convert.ToInt32(dr["BerufstitelKey"]);
                    bo.BerufsTitel = Convert.ToString(dr["BerufstitelBez"]);
                    ret.Add(bo);
                }
            }
            return ret;
        }

        public virtual FunktionsObj GetFunktion(int funktionsKey) {
            FunktionsObj fo = new FunktionsObj();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(this.loadFunktionsStatement, new SqlParameter("@FunktionsKey", funktionsKey))) {
                foreach (DataRow dr in dt.Rows) {
                    fo.FunktionsKey = Convert.ToInt32(dr["FunktionsKey"]);
                    fo.Funktion = Convert.ToString(dr["FunktionsBez"]);
                }
            }
            return fo;
        }

        public virtual List<FunktionsObj> GetFunktionList() {
            List<FunktionsObj> ret = new List<FunktionsObj>();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(this.loadFunktionsStatement)) {
                foreach (DataRow dr in dt.Rows) {
                    FunktionsObj fo = new FunktionsObj();
                    fo.FunktionsKey = Convert.ToInt32(dr["FunktionsKey"]);
                    fo.Funktion = Convert.ToString(dr["FunktionsBez"]);
                    ret.Add(fo);
                }
            }
            return ret;
        }

        public virtual RechtsformObject GetRechtsform(int rechtsformKey) {
            RechtsformObject rf = new RechtsformObject();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(this.loadRechtsformStatement, new SqlParameter("@RechtsformKey", rechtsformKey))) {
                foreach (DataRow dr in dt.Rows) {
                    rf.RechtsformKey = Convert.ToInt32(dr["RechtsFKey"]);
                    rf.Rechtsform = Convert.ToString(dr["RechtsFBez"]);
                }
            }
            return rf;
        }

        public virtual List<RechtsformObject> GetRechtsformList() {
            List<RechtsformObject> ret = new List<RechtsformObject>();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(this.loadRechtsformStatement)) {
                foreach (DataRow dr in dt.Rows) {
                    RechtsformObject rf = new RechtsformObject();
                    rf.RechtsformKey = Convert.ToInt32(dr["RechtsFKey"]);
                    rf.Rechtsform = Convert.ToString(dr["RechtsFBez"]);
                    ret.Add(rf);
                }
            }
            return ret;
        }

        public virtual Organisation LoadOrganisation(string name, string suchbegriff) {
            using (IDataReader reader = this.GetDataReaderFromStoredProcedure(loadOrganisationStatement, new SqlParameter("@Name", name), new SqlParameter("@Suchbegriff", suchbegriff))) {
                return (reader.Read() ? LoadOrganisationFromDataReader(reader) : null);
            }
        }

        public virtual Organisation LoadOrganisation(string name, int plz) {
            using (IDataReader reader = this.GetDataReaderFromStoredProcedure(loadOrganisationStatement, new SqlParameter("@Name", name), new SqlParameter("@Plz", plz))) {
                return (reader.Read() ? LoadOrganisationFromDataReader(reader) : null);
            }
        }

        public virtual Organisation LoadOrganisation(int orgKey) {
            using (IDataReader reader = this.GetDataReaderFromStoredProcedure(loadOrganisationStatement, new SqlParameter("@OrgKey", orgKey))) {
                return (reader.Read() ? LoadOrganisationFromDataReader(reader) : null);
            }
        }

        public virtual Organisation LoadOrganisation(string suchBegriff) {
            using (IDataReader reader = this.GetDataReaderFromStoredProcedure(loadOrganisationStatement, new SqlParameter("@SuchBegriff", suchBegriff))) {
                return (reader.Read() ? LoadOrganisationFromDataReader(reader) : null);
            }
        }

        public virtual DataTable LoadOrganisationDataTable() {
            using (DataTable dt = this.GetDataTable(loadOrganisationStatement)) {
                return dt;
            }
        }
        
        public virtual List<OrganisationsShortListObj> LoadOrganisationsShortList() {
            List<OrganisationsShortListObj> retList = new List<OrganisationsShortListObj>();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(this.loadOrganisationsShortListStatement)) {
                foreach (DataRow dr in dt.Rows) {
                    OrganisationsShortListObj ret = new OrganisationsShortListObj();
                    ret.Abteilung = Convert.ToString(dr["Abteilung"]);
                    ret.Adresse = Convert.ToString(dr["Adresse"]);
                    ret.OrgKey = Convert.ToInt32(dr["OrgKey"]);
                    ret.OrgName = Convert.ToString(dr["OrgName"]);
                    ret.Prefix = Convert.ToString(dr["Prefix"]);
                    ret.RechtsForm = Convert.ToString(dr["RechtsForm"]);
                    ret.Strasse = Convert.ToString(dr["Strasse"]);
                    ret.Suchbegriff = Convert.ToString(dr["Suchbegriff"]);
                    ret.ZusätzeFW = Convert.ToString(dr["ZusätzeFW"]);
                }
            }
            return retList;
        }

        public virtual List<Person> LoadPersons(int persKey) {
            List<Person> ret = new List<Person>();
            SqlParameter[] par = new SqlParameter[1];
            if (persKey != 0) {
                par[0] = new SqlParameter("@PersKey", persKey);
            }
            using (DataTable dt = this.GetDataTableFromStoredProcedure(this.loadPersonsStatement, par)) {
                foreach (DataRow dr in dt.Rows) {
                    Person p = new Person();
                    p.PersKey = Convert.ToInt32(dr["PersKey"]);
                    p.PersTS = Convert.ToString(dr["PersTS"]);
                    p.Änderung = Convert.ToString(dr["Änderung"]);
                    p.Zuname = Convert.ToString(dr["Zuname"]);
                    p.Vorname = Convert.ToString(dr["Vorname"]);
                    p.Geschlecht = (short)Convert.ToUInt16(dr["Geschlecht"]);
                    if (Convert.ToString(dr["TitelKey1"]) != "") {
                        p.Titel1Key = Convert.ToInt32(dr["TitelKey1"]);
                    }
                    if (Convert.ToString(dr["TitelKey2"]) != "") {
                        p.Titel2Key = Convert.ToInt32(dr["TitelKey2"]);
                    }
                    p.Adresse = Convert.ToString(dr["Adresse"]);
                    if (Convert.ToString(dr["EPLZ"]) != "") {
                        p.EPLZ = Convert.ToInt32(dr["EPLZ"]);
                    }
                    p.Straße = Convert.ToString(dr["Straße"]);
                    if (Convert.ToString(dr["VorwahlKey"]) != "") {
                        p.VorwahlKey = Convert.ToInt32(dr["VorwahlKey"]);
                    }
                    p.Mobil = Convert.ToString(dr["Mobil"]);
                    p.PrivTelefon = Convert.ToString(dr["PrivTelefon"]);
                    p.PrivFax = Convert.ToString(dr["PrivFax"]);
                    p.MailingSperre = Convert.ToBoolean(dr["MailingSperre"]);
                    p.Notiz = Convert.ToString(dr["Notiz"]);
                    p.Mitarbeiter = Convert.ToBoolean(dr["Mitarbeiter"]);
                    p.Kürzel = Convert.ToString(dr["Kürzel"]);
                    p.EMail = Convert.ToString(dr["EMail"]);
                    ret.Add(p);
                }
            }
            return ret;
        }

        public virtual List<Organisation> SearchOrganisation(string orgName) {
            List<Organisation> ret = new List<Organisation>();

            using (DataTable dt = this.GetDataTable(loadOrganisationStatement, new SqlParameter("@Name", orgName))) {
                foreach (DataRow dr in dt.Rows) {
                    Organisation org = new Organisation();
                    org.OrgKey = (int)dr["OrgKey"];
                    org.OrgTS = Convert.ToString(dr["OrgTS"]);
                    org.Änderung = (DateTime)dr["LastChangedOn"];
                    org.OrgName = Convert.ToString(dr["Name"]);
                    org.ZusätzeFW = Convert.ToString(dr["ZusätzeFW"]);
                    org.RechtsForm = Convert.ToString(dr["RechtsFBez"]);
                    org.Suchbegriff = Convert.ToString(dr["Suchbegriff"]);
                    org.Abteilung = Convert.ToString(dr["Abteilung"]);
                    org.Adresse = Convert.ToString(dr["Adresse"]);
                    org.EPLZ = (int)dr["EPLZ"];
                    org.Straße = Convert.ToString(dr["Straße"]);
                    org.TelNr1 = Convert.ToString(dr["TelNr1"]);
                    org.TelNr2 = Convert.ToString(dr["TelNr2"]);
                    org.FaxNr = Convert.ToString(dr["FaxNr"]);
                    org.EMail = Convert.ToString(dr["e_Mail"]);
                    org.Notiz = Convert.ToString(dr["Notiz"]);
                    org.Export = (bool)dr["export"];
                    org.Plz = Convert.ToString(dr["Plz"]);
                    org.Ort = Convert.ToString(dr["Ort"]);
                    org.Prefix = Convert.ToString(dr["Prefix"]);
                    org.UID = Convert.ToString(dr["UID"]);
                    org.Web = Convert.ToString(dr["Web"]);
                    ret.Add(org);
                }
            }

            return ret;
        }

        public virtual List<Organisation> SearchOrganisationStreet(string strasse) {
            List<Organisation> ret = new List<Organisation>();
            using (DataTable dt = this.GetDataTable(loadOrganisationStatement, new SqlParameter("@Strasse", strasse))) {
                foreach (DataRow dr in dt.Rows) {
                    Organisation org = new Organisation();
                    org.OrgKey = (int)dr["OrgKey"];
                    org.OrgTS = Convert.ToString(dr["OrgTS"]);
                    org.Änderung = (DateTime)dr["LastChangedOn"];
                    org.OrgName = Convert.ToString(dr["Name"]);
                    org.ZusätzeFW = Convert.ToString(dr["ZusätzeFW"]);
                    org.RechtsForm = Convert.ToString(dr["RechtsFBez"]);
                    org.Suchbegriff = Convert.ToString(dr["Suchbegriff"]);
                    org.Abteilung = Convert.ToString(dr["Abteilung"]);
                    org.Adresse = Convert.ToString(dr["Adresse"]);
                    org.EPLZ = (int)dr["EPLZ"];
                    org.Straße = Convert.ToString(dr["Straße"]);
                    org.TelNr1 = Convert.ToString(dr["TelNr1"]);
                    org.TelNr2 = Convert.ToString(dr["TelNr2"]);
                    org.FaxNr = Convert.ToString(dr["FaxNr"]);
                    org.EMail = Convert.ToString(dr["e_Mail"]);
                    org.Notiz = Convert.ToString(dr["Notiz"]);
                    org.Export = (bool)dr["export"];
                    org.Plz = Convert.ToString(dr["Plz"]);
                    org.Ort = Convert.ToString(dr["Ort"]);
                    org.Prefix = Convert.ToString(dr["Prefix"]);
                    org.UID = Convert.ToString(dr["UID"]);
                    org.Web = Convert.ToString(dr["Web"]);
                    ret.Add(org);
                }
            }

            return ret;
        }

        #endregion

        #region Save

        public virtual bool SaveOrganisation(Organisation model) {
            int ret = 0;
            SqlParameter[] parameters = GetParameters(model);
            if (model.OrgKey != 0) {
                ret = ExecuteStoredProcedure(saveOrganisationStatement, parameters);
                if (ret != -1) {
                    return false;
                }
                model.IsDirty = false;
                return true;
            }
            using (IDataReader reader = this.GetDataReaderFromStoredProcedure(saveOrganisationStatement, parameters)) {
                if (reader.Read()) {
                    if (this.GetInt32(reader, "OrgKey") != 0) {
                        model.OrgKey = this.GetInt32(reader, "OrgKey");
                    }
                    if (this.GetInt32(reader, "Return") != 0) {
                        return false;
                    }
                }
            }
            model.IsDirty = false;
            return true;
        }

        public virtual bool SavePerson(Person model) {
            int ret = 0;
            SqlParameter[] parameters = GetParameters(model);
            if (model.OrgKey != 0) {
                ret = ExecuteStoredProcedure(savePersonStatement, parameters);
                if (ret != -1) {
                    return false;
                }
                model.IsDirty = false;
                return true;
            }
            using (IDataReader reader = this.GetDataReaderFromStoredProcedure(savePersonStatement, parameters)) {
                if (reader.Read()) {
                    if (this.GetInt32(reader, "OrgKey") != 0) {
                        model.OrgKey = this.GetInt32(reader, "OrgKey");
                    }
                    if (this.GetInt32(reader, "Return") != 0) {
                        return false;
                    }
                }
            }
            model.IsDirty = false;
            return true;
        }

        #endregion

        #region Functions

        public virtual void FillMyPersonList(Organisation org) {
            using (IDataReader reader = this.GetDataReaderFromStoredProcedure(loadOrganisationPersonsStatement, new SqlParameter("@OrgKey", org.OrgKey))) {
                while (reader.Read()) {
                    Person pers = new Person();
                    pers.Adresse = this.GetString(reader, "Adresse");
                    //pers.Änderung = this.GetString(reader, "LastChangedOn");
                    pers.BerufstitelKey = this.GetInt32(reader, "BerufstitelKey");
                    pers.Durchwahl = this.GetString(reader, "Durchwahl");
                    pers.EMail = this.GetString(reader, "eMail");
                    pers.EPLZ = this.GetInt32(reader, "EPLZ");
                    pers.Fax = this.GetString(reader, "Fax");
                    pers.FunktionsKey = this.GetInt32(reader, "FunktionsKey");
                    pers.Geschlecht = (short)this.GetInt32(reader, "Geschlecht");
                    pers.Kürzel = this.GetString(reader, "Kürzel");
                    pers.MailingSperre = this.GetBoolean(reader, "MailingSperre");
                    pers.Mitarbeiter = this.GetBoolean(reader, "Mitarbeiter");
                    pers.Mobil = this.GetString(reader, "Mobil");
                    pers.Notiz = this.GetString(reader, "Notiz");
                    pers.OrgKey = this.GetInt32(reader, "OrgKey");
                    pers.PersKey = this.GetInt32(reader, "PersKey");
                    pers.PersTS = this.GetString(reader, "PersTS");
                    pers.PrivFax = this.GetString(reader, "PrivFax");
                    pers.PrivTelefon = this.GetString(reader, "PrivTelefon");
                    pers.Straße = this.GetString(reader, "Straße");
                    pers.Tätigkeit = this.GetString(reader, "Job");
                    pers.Titel1Key = this.GetInt32(reader, "TitelKey1");
                    pers.Titel2Key = this.GetInt32(reader, "TitelKey2");
                    pers.Vorname = this.GetString(reader, "Vorname");
                    pers.VorwahlKey = this.GetInt32(reader, "VorwahlKey");
                    pers.Zuname = this.GetString(reader, "Zuname");

                    org.AddPerson(pers);

                }
            }
        }

        private DataTable GetPersOrgDataTable() {

            DataTable dt = new DataTable();
            DataColumn dc = new DataColumn("OrgKey");
            dc.DataType = Type.GetType("System.Int32");
            dt.Columns.Add(dc);

            dc = new DataColumn("PersKey");
            dc.DataType = Type.GetType("System.Int32");
            dt.Columns.Add(dc);

            dc = new DataColumn("BerufstitelKey");
            dc.DataType = Type.GetType("System.Int32");
            dt.Columns.Add(dc);

            dc = new DataColumn("FunktionsKey");
            dc.DataType = Type.GetType("System.Int32");
            dt.Columns.Add(dc);

            dc = new DataColumn("Durchwahl");
            dc.DataType = Type.GetType("System.String");
            dt.Columns.Add(dc);

            dc = new DataColumn("Fax");
            dc.DataType = Type.GetType("System.String");
            dt.Columns.Add(dc);

            dc = new DataColumn("Tätigkeit");
            dc.DataType = Type.GetType("System.String");
            dt.Columns.Add(dc);

            return dt;
        }

        protected virtual SqlParameter[] GetParameters(Organisation model) {

            DataTable dt = GetPersOrgDataTable();

            foreach (Person p in model.MyPersons) {
                DataRow dr = dt.NewRow();
                dr["OrgKey"] = p.OrgKey;
                dr["PersKey"] = p.PersKey;
                dr["BerufstitelKey"] = p.BerufstitelKey;
                dr["FunktionsKey"] = p.FunktionsKey;
                dr["Durchwahl"] = p.Durchwahl;
                dr["Fax"] = p.Fax;
                dr["Tätigkeit"] = p.Tätigkeit;
                dt.Rows.Add(dr);
            }
            
            SqlParameter[] ps = new SqlParameter[22];

            ps[0] = new SqlParameter("@OrgKey", model.OrgKey);
            ps[1] = new SqlParameter("@OrgTS", model.OrgTS);
            ps[2] = new SqlParameter("@Prefix", model.Prefix);
            ps[3] = new SqlParameter("@OrgName", model.OrgName);
            ps[4] = new SqlParameter("@ZusätzeFW", model.ZusätzeFW);
            ps[5] = new SqlParameter("@RechtsFKey", model.RechtsFormKey);
            ps[6] = new SqlParameter("@Suchbegriff", model.Suchbegriff);
            ps[7] = new SqlParameter("@Abteilung", model.Abteilung);
            ps[8] = new SqlParameter("@Adresse", model.Adresse);
            ps[9] = new SqlParameter("@EPLZ", model.EPLZ);
            ps[10] = new SqlParameter("@Straße", model.Straße);
            ps[11] = new SqlParameter("@TelNr1", model.TelNr1);
            ps[12] = new SqlParameter("@TelNr2", model.TelNr2);
            ps[13] = new SqlParameter("@FaxNr", model.FaxNr);
            ps[14] = new SqlParameter("@EMail", model.EMail);
            ps[15] = new SqlParameter("@Kurzwahlspeicher", model.Kurzwahlspeicher);
            ps[16] = new SqlParameter("@Notiz", model.Notiz);
            ps[17] = new SqlParameter("@Export", model.Export);
            ps[18] = new SqlParameter("@UID", model.UID);
            ps[19] = new SqlParameter("@UserID", Contracts.MyUser.UserID);
            ps[20] = new SqlParameter("@Web", model.Web);
            ps[21] = new SqlParameter("@MyPersons", dt);

            return ps;
        }

        protected virtual SqlParameter[] GetParameters(Person model) {
            SqlParameter[] ps = new SqlParameter[22];

            ps[0] = new SqlParameter("@PersKey", model.PersKey);
            ps[1] = new SqlParameter("@PersTS", model.PersTS);
            ps[2] = new SqlParameter("@OrgKey", model.OrgKey);
            ps[3] = new SqlParameter("@Änderung", model.Änderung);
            ps[4] = new SqlParameter("@Durchwahl", model.Durchwahl);
            ps[5] = new SqlParameter("@Zuname", model.Zuname);
            ps[6] = new SqlParameter("@Vorname", model.Vorname);
            ps[7] = new SqlParameter("@Geschlecht", model.Geschlecht);
            ps[8] = new SqlParameter("@Titel1Key", model.Titel1Key);
            ps[9] = new SqlParameter("@Titel2Key", model.Titel2Key);
            ps[10] = new SqlParameter("@Adresse", model.Adresse);
            ps[11] = new SqlParameter("@EPLZ", model.EPLZ);
            ps[12] = new SqlParameter("@Straße", model.Straße);
            ps[13] = new SqlParameter("@VorwahlKey", model.VorwahlKey);
            ps[14] = new SqlParameter("@Mobil", model.Mobil);
            ps[15] = new SqlParameter("@PrivTelefon", model.PrivTelefon);
            ps[16] = new SqlParameter("@PrivFax", model.PrivFax);
            ps[17] = new SqlParameter("@MailingSperre", model.MailingSperre);
            ps[18] = new SqlParameter("@Notiz", model.Notiz);
            ps[19] = new SqlParameter("@Mitarbeiter", model.Mitarbeiter);
            ps[20] = new SqlParameter("@Kürzel", model.Kürzel);
            ps[21] = new SqlParameter("@EMail", model.EMail);
            
            return ps;
        }

        protected virtual Organisation LoadOrganisationFromDataReader(IDataReader reader) {
            Organisation org = new Organisation();
            org.OrgKey = this.GetInt32(reader, "OrgKey");
            org.OrgTS = this.GetString(reader, "OrgTS");
            org.Änderung = this.GetDateTime(reader, "LastChangedOn");
            org.OrgName = this.GetString(reader, "OrgName");
            org.ZusätzeFW = this.GetString(reader, "ZusätzeFW");
            org.RechtsForm = this.GetString(reader, "RechtsFBez");
            org.RechtsFormKey = this.GetInt32(reader, "RechtsFKey");
            org.Suchbegriff = this.GetString(reader, "Suchbegriff");
            org.Abteilung = this.GetString(reader, "Abteilung");
            org.Adresse = this.GetString(reader, "Adresse");
            org.EPLZ = this.GetInt32(reader, "EPLZ");
            org.Straße = this.GetString(reader, "Straße");
            org.TelNr1 = this.GetString(reader, "TelNr1");
            org.TelNr2 = this.GetString(reader, "TelNr2");
            org.FaxNr = this.GetString(reader, "FaxNr");
            org.EMail = this.GetString(reader, "EMail");
            org.Kurzwahlspeicher = this.GetString(reader, "Kurzwahlspeicher");
            org.KG = this.GetBoolean(reader, "KG");
            org.MB = this.GetBoolean(reader, "MB");
            org.TochterVon = this.GetInt32(reader, "TochterVon");
            org.Kreditlimit = this.GetInt32(reader, "Kreditlimit");
            org.Notiz = this.GetString(reader, "Notiz");
            if (Convert.ToString(reader["Export"]) != "0") {
                org.Export = true;
            }
            org.Plz = this.GetString(reader, "Plz").ToString();
            org.Ort = this.GetString(reader, "Ort");
            org.Prefix = this.GetString(reader, "Prefix");
            org.UID = this.GetString(reader, "UID");
            org.Web = this.GetString(reader, "Web");

            FillMyPersonList(org);

            return org;
        }

        #endregion

    }
}
